#!/bin/bash
# **********************************************************
# * Author        : zhaoxunfeng
# * Email         : m15675325027@163.com
# * Create time   : 2022-07-28 11:09
# * Filename      : dwd_to_dws_1d_init.sh
# * Description   : dws层最近1日 首日数据装载脚本
# **********************************************************
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

dws_study_course_all_complete_1d="
insert overwrite table dws_study_course_all_complete_1d partition (dt = '2022-02-21')
select course_id,
       course_name,
       count(distinct user_id) total_user_nums_1d,
       count(user_id)          total_user_times_1d
from dws_study_user_course_complete_play_td
where dt = '2022-02-21'
group by course_id, course_name;
"

dws_study_user_chapter_play_1d="
insert overwrite table dws_study_user_chapter_play_1d partition (dt = '2022-02-22')
select user_id,
       chapter_id,
       chapter_name,
       count(*)                play_count_1d,
       sum(play_duration)      play_duration_1d,
       count(distinct user_id) user_count_1d
from dwd_study_play_video_inc
where dt = '2022-02-22'
group by user_id, chapter_id, chapter_name;
"

dws_study_user_course_complete_play_1d="
  set hive.exec.dynamic.partition.mode=nonstrict;
  insert overwrite table dws_study_user_course_complete_play_1d partition (dt)
  select course_id,
         course_name,
         count(distinct user_id),
         count(user_id),
         dt
  from dws_study_user_course_complete_play_td
  where dt = '2022-02-21'
  group by course_id, course_name, dt;
"

dws_study_user_course_play_1d="
  set hive.exec.dynamic.partition.mode=nonstrict;
  insert overwrite table dws_study_user_course_play_1d partition (dt)
  select user_id,
         course_id,
         course_name,
         count(video_id)         play_count_1d,
         sum(play_duration)      play_duration_1d,
         count(distinct user_id) user_count_1d,
         dt
  from dwd_study_play_video_inc
  group by user_id, course_id, course_name,dt;
"
dws_interaction_review_by_course_1d="
set hive.exec.dynamic.partition.mode=nonstict;
with review as
(
    select
        dt,
        course_id,
        count(user_id) review_user_num,
        count(if(review_stars = 5,user_id,null)) favorable_num,
        sum(review_stars) sum_review_stars,
        avg(review_stars) avg_review_stars
    from dwd_interaction_review_inc
    where dt <= '2022-02-21'
    group by course_id,dt
),
course as
(
    select
        id,
        course_name,
        course_slogan,
        course_cover_url,
        teacher,
        publisher_id,
        subject_id,
        subject_name,
        category_id,
        category_name,
        chapter_num,
        origin_price,
        reduce_amount,
        actual_price,
        course_introduce
    from dim_course_full
    where dt = '2022-02-21'
)
insert overwrite table dws_interaction_review_by_course_1d partition (dt)
select
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    review_user_num,
    favorable_num,
    sum_review_stars,
    avg_review_stars,
    review.dt
from review full join course on review.course_id=course.id;
"

dws_interaction_user_trial_1d="
with video as
(
    select
        id,
        course_id
    from dim_video_full
    where dt = '2022-02-22'
),
--课程维度
course as
(
    select
        id,
        course_name,
        subject_id,
        subject_name,
        category_id,
        category_name
    from dim_course_full
    where dt = '2022-02-22'
),
     --历史下单用户信息表
user_order_course as
(
    select
        order_id,
        user_id,
        course_id,
        create_time order_time,
        date_id
    from dwd_trade_order_detail_inc
),
--当日首次播放视频
plays as
(
    select
        uid,
        sid,
        video_id,
        plays_time,
        to_date(plays_time) date_id
    from
    (
        select
            uid,
            sid,
            video_id,
            from_unixtime(cast(ts/1000 as bigint),'yyyy-MM-dd HH:mm:ss') plays_time,
            row_number() over (partition by uid,sid,video_id order by ts) rk
        from dwd_interaction_plays_inc
        where dt='2022-02-22'
    ) t1
    where rk = 1
),
     --当日用户播放课程视频记录
play_course as
(
    select
        uid,
        sid,
        video_id,
        plays_time,
        course_id,
        course_name,
        subject_id,
        subject_name,
        category_id,
        category_name,
        plays.date_id
    from plays
        left join video on plays.video_id =  video.id
        left join course on video.course_id = course.id
)
insert overwrite table dws_interaction_user_trial_1d partition(dt='2022-02-22')
select
    uid,
    sid,
    pc.course_id,
    pc.course_name,
    pc.subject_id,
    pc.subject_name,
    pc.category_id,
    pc.category_name,

    plays_time trial_time,
    pc.date_id
from play_course pc left join user_order_course uoc on pc.uid = uoc.user_id
    and pc.course_id = uoc.course_id
where (user_id is null and uoc.course_id is null) or (plays_time<order_time);
"

dws_traffic_session_page_view_1d="
with t1 as (
    select
        sid,
        mid,
        ba,
        md,
        os,
        sc,
        sum(during_time) during_time_1d,
        count(*) page_count_1d
    from dwd_traffic_page_view_inc
    where dt='2022-02-21'
    group by sid, mid, ba, md, os, sc),
     t2 as (
         select id, source_site, source_url
         from ods_base_source_full
         where dt = '2022-02-21'
     )
insert overwrite table dws_traffic_session_page_view_1d partition(dt='2022-02-21')
select sid,
       mid,
       ba,
       md,
       os,
       sc,
       source_site,
       source_url,
       during_time_1d,
       page_count_1d
from t1 left join t2 on t1.sc = t2.id;
"

dws_traffic_visitor_page_view_1d="
insert overwrite table dws_traffic_visitor_page_view_1d partition(dt='2022-02-21')
select
    mid,
    uid,
    ba,
    md,
    os,
    page_id,
    sum(during_time),
    count(*)
from dwd_traffic_page_view_inc
where dt='2022-02-21'
group by mid, uid, ba, md, os, page_id;
"

dws_trade_user_cart_add_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_cart_add_1d partition(dt)
select
    user_id,
    count(*),
    count(*),
    dt
from dwd_trade_cart_add_inc
group by user_id,dt;
"

dws_trade_user_payment_1d="
insert overwrite table dws_trade_user_payment_1d partition (dt)
select
    user_id,
    count(distinct order_id),
    count(*),
    sum(pay_amount),
    dt
from dwd_trade_payment_inc
group by dt, user_id;
"

dws_trade_user_order_1d="
  set hive.exec.dynamic.partition.mode=nonstrict;
  insert overwrite table dws_trade_user_order_1d partition (dt)
  select
      user_id,
      count(distinct order_id) order_count_1d,
      count(user_id) order_num_1d,
      sum(origin_amount) order_original_amount_1d,
      sum(coupon_reduce) coupon_reduce_amount_1d,
      sum(final_amount) order_total_amount_1d,
      dt
  from dwd_trade_order_detail_inc
  group by dt, user_id;
"

dws_test_scoreGap_num_1d="
insert overwrite table dws_test_scoreGap_num_1d partition(dt='2022-02-22')
select
    paper_id,
    sum(if(score>0 and score<=59,1,0)) count_user_0_59 ,
    sum(if(score>59 and score<=79,1,0)) count_user_60_79,
    sum(if(score>79 and score<=100,1,0)) count_user_80_100
from dwd_test_test_exam_inc
where dt='2022-02-22'
group by paper_id , date_id;
"

dws_test_paper_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_test_paper_1d partition(dt)
select
    paper_id,
    count(distinct (user_id)) test_count_1d,
    avg(duration_sec) avg_test_time_1d,
    avg(score) avg_test_score_1d,
    sum(duration_sec) sum_test_time_1d,
    sum(score) sum_test_score_1d,
    dt
from dwd_test_test_exam_inc
group by paper_id,dt;
"

dws_test_course_1d="
set hive.exec.dynamic.partition.mode=nonstrict;
with A AS (
select
    paper_id,
    user_id,
    duration_sec,
    score,
    date_id
from dwd_test_test_exam_inc
where dt ='2022-02-21'
    ),
B as (
    select
            id ,
            course_id,
            course_name
    from dim_paper_full
    where dt='2022-02-21'
)
insert overwrite table dws_test_course_1d partition(dt)
select
    paper_id,
    course_id,
    course_name,
    count(distinct (user_id)) course_test_count_1d,
    avg(duration_sec) avg_course_test_time_1d,
    avg(score) avg_course_test_user_1d,
    sum(duration_sec) sum_course_test_time_1d,
    sum(score) sum_course_test_score_1d,
    date_id
from A join B
on A.paper_id=B.id
group by paper_id,
         course_id,
          course_name,
          date_id;
"

dws_test_question_times_1d="
insert overwrite table dws_test_question_times_1d partition(dt='2022-02-22')
select
    question_id,
    count(user_id) count_question_total_times_1d,
    sum(if(is_correct=1,1,0)) count_question_pass_times_1d
from dwd_test_question_test_exam_inc
where dt='2022-02-22'
group by question_id;
"